<?php
/**
 * Created by PhpStorm.
 * User: 明哲
 * Date: 2016/4/28
 * Time: 14:53
 */
include('../../config.php');

$action = $_GET['action'];
if ($action == 'import') { //导入XLS
    include_once("../../utils/Excel/reader.php"); //引入PHP-ExcelReader
    $tmp = $_FILES['xls-file']['tmp_name'];
    if (empty ($tmp)) {
        echo "请选择要导入的Excel文件";
        exit;
    }
//    $time = date('Y-m-d',time());
    $submitTime = date('Y-m-d h:i:sa',time());
    $save_path = "../../xls/";
    $file_name = $save_path.date('Ymdhis'). ".xls"; //上传后的文件保存路径和名称
    if (copy($tmp, $file_name)) {
        $xls = new Spreadsheet_Excel_Reader();
        $xls->setOutputEncoding('utf-8');  //设置编码
        $xls->read($file_name);  //解析文件
        $SN=0;
        for ($i=2; $i<=$xls->sheets[0]['numRows']; $i++) {
            $time = $xls->sheets[0]['cells'][$i][1];
            $pipe_name = $xls->sheets[0]['cells'][$i][2];
            $product_name = $xls->sheets[0]['cells'][$i][3];
            $product_name_simple = substr($product_name,0,strlen($product_name)-2);
            $new_user_number = $xls->sheets[0]['cells'][$i][4];
            $active_user = $xls->sheets[0]['cells'][$i][5];
            $money_all_info = $xls->sheets[0]['cells'][$i][6];
            $display_rate = $xls->sheets[0]['cells'][$i][7];
            $sale_name = $xls->sheets[0]['cells'][$i][8];
            $sf_new_user_number = $xls->sheets[0]['cells'][$i][9];
            $status = 0;
            $active_user_ck=-1;

            $row=$mysql->query("select `pipe_type`,`unit_price`,`clear_cycle` from `pipe` where `pipe_name`='{$pipe_name}'");
            $pipe_type=$row[0]['pipe_type'];
            $unit_price=$row[0]['unit_price'];
            $clear=$row[0]['clear_cycle'];

            //查询数据是否重复
            $result=$mysql->query("select `id` from `product_data` where `pipe_name` = '{$pipe_name}' and  `time` = '{$time}' and `product_name` = '{$product_name}' limit 1");
            if($result){
                echo '<script>alert("第'.($i-1).'条数据重复")</script>';
                continue;
            }
            //检查是否建立相应的渠道账户
//            var_dump($pipe_name);
//            var_dump($product_name_simple);
            $rows = $mysql->query("select 'pipe_name' from `pipe` where `pipe_name`='{$pipe_name}' and `product_name` like '%{$product_name_simple}%' limit 1");
            if(!$rows){
                //如果不存在则跳出本次循环
                echo '<script>alert("'.$pipe_name.' 未建立渠道账户或未为此渠道添加产品,此条记录添加失败")</script>';
                continue;
            }
            if($pipe_type==0){
                $money_display=0;
                $benefit=0;
                $benefit_rate=0;
            }elseif($pipe_type==1){
                $money_display=$money_all_info*$display_rate;
                $benefit=$money_all_info*0.4-$money_display*$clear;
                $benefit_rate=$benefit/$money_all_info;
            }
            $data_values .= "('null','$time','$product_name','$sale_name','$pipe_name','$new_user_number','$sf_new_user_number','$active_user','$active_user_ck','$money_all_info','$display_rate','$submitTime','$status','$pipe_type','$unit_price','$money_display','$benefit','$benefit_rate'),";
            $SN++;
        }
        $data_values = substr($data_values,0,-1); //去掉最后一个逗号
//        var_dump($data_values);
        $query = $mysql->query("insert into `product_data` (`id`,`time`,`product_name`,`sale_name`,`pipe_name`,`new_user_number`,`sf_new_user_number`,`active_user`,`active_user_ck`,`money_all_info`,`display_rate`,`submit_date`,`status`,`pipe_type`,`money_rate`,`money_display`,`benefit`,`benefit_rate`) values $data_values");//批量插入数据表中
        echo "<script>alert('完成，成功上传".$SN."条数据');window.location = '../index.php?p=data_mani';</script>";
    }
} elseif ($action=='export') { //导出XLS
        $startTime =  $_GET['startTime'];
        $endTime =  $_GET['endTime'];
    if($startTime==''&&$endTime==''){
        $current = date('Y-m-d',time());
        $rows=mysql_query("select * from `product_data` WHERE `time`='{$current}'  order by time DESC");
    }else{
        $rows=mysql_query("select * from `product_data` WHERE `time` BETWEEN '{$startTime}' and '{$endTime}'  order by time DESC");
    }
    $str = "产品名称\t销售名称\t渠道名称\t新增用户\t第三方新增用户\t激活用户\t激活用户参考\t渠道类型\t单价分成\t总信息费\t显示比例\t显示金额\t利润\t利润率\t审核状态\t提交日期\t\n";
    $str = iconv('gbk','utf-8//IGNORE',$str);
    while($row=mysql_fetch_array($rows)){
        $product_name = iconv('utf-8','gbk',$row['product_name']);
        $sale_name = iconv('utf-8','gbk',$row['sale_name']);
        $pipe_name = iconv('utf-8','gbk',$row['pipe_name']);
        $new_user_number = iconv('utf-8','gbk',$row['new_user_number']);
        $sf_new_user_number = iconv('utf-8','gbk',$row['sf_new_user_number']);
        $active_user = iconv('utf-8','gbk',$row['active_user']);
        $active_user_ck = iconv('utf-8','gbk',$row['active_user_ck']);
        $pipe_type = iconv('utf-8','gbk',$row['pipe_type']);
        $money_rate = iconv('utf-8','gbk',$row['money_rate']);
        $money_all_info = iconv('utf-8','gbk',$row['money_all_info']);
        $display_rate = iconv('utf-8','gbk',$row['display_rate']);
        $money_display = iconv('utf-8','gbk',$row['money_display']);
        $submit_date = iconv('utf-8','gbk',$row['submit_date']);
        $benefit = iconv('utf-8','gbk',$row['benefit']);
        $benefit_rate = iconv('utf-8','gbk',$row['benefit_rate']);
        $status = iconv('utf-8','gbk',$row['status']);
        $str .= $product_name."\t".$sale_name."\t".$pipe_name."\t".$new_user_number."\t".$sf_new_user_number."\t".$active_user."\t".$active_user_ck."\t".$pipe_type."\t".$money_rate."\t".$money_all_info."\t".$display_rate."\t".$money_display."\t".$benefit."\t".$benefit_rate."\t".$status."\t".$submit_date."\t\n";
    }
    $filename = date('Ymdhis').'.xls';
    exportExcel($filename,$str);
}


function exportExcel($filename,$content){
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Type: application/vnd.ms-execl;charset=utf8");
    header("Content-Type: application/force-download");
    header("Content-Type: application/download");
    header("Content-Disposition: attachment; filename=".$filename);
    header("Content-Transfer-Encoding: binary");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo $content;
//    readfile($content);
}
?>